package br.com.revenda.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


import br.com.revenda.conexao.connectionFactory;
import br.com.revenda.modelo.Funcionario;

public class FuncionarioDAO {

	private Connection connection;
	
	public FuncionarioDAO(){
	this.connection = new connectionFactory().getConnection();
	}
	
	
	public void salvar(Funcionario fun){
	String sql = "insert into funcionario (nome , dataNasc , cpf , rg , fone , celular , endereco , cidade , bairro , estado , dtAdm, cargo, porcentagem)values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
	try {
		java.sql.PreparedStatement pstm = connection.prepareStatement(sql);
		pstm.setString(1, fun.getNome());
		pstm.setString(2, fun.getDataNasc());
		pstm.setString(3, fun.getCpf());
		pstm.setString(4, fun.getRg());
		pstm.setString(5, fun.getFone());
		pstm.setString(6, fun.getCelular());
		pstm.setString(7, fun.getEndereco());
		pstm.setString(8, fun.getCidade());
		pstm.setString(9, fun.getBairro());
		pstm.setString(10, fun.getEstado());
		pstm.setString(11, fun.getDtAdm());
		pstm.setString(12, fun.getCargo());
		pstm.setDouble(13, fun.getPorcentagem());
		pstm.execute();
		pstm.close();
		connection.close();
	} catch (SQLException e) {
		throw new RuntimeException(e);
		}			
	}
	
	public List<Funcionario> ListarTodos(){
		List<Funcionario> f = new ArrayList<Funcionario>();
		String sql = "select * from funcionario";
		try {
			PreparedStatement pstm = connection.prepareStatement(sql);
			ResultSet rs = pstm.executeQuery();
			
			while(rs.next()){
				Funcionario func = new Funcionario();
				func.setId(rs.getInt("id"));
				func.setNome(rs.getString("nome"));
				func.setDataNasc(rs.getString("dataNasc"));
				func.setCpf(rs.getString("cpf"));
				func.setRg(rs.getString("rg"));
				func.setFone(rs.getString("fone"));
				func.setCelular(rs.getString("celular"));
				func.setEndereco(rs.getString("endereco"));
				func.setCidade(rs.getString("cidade"));
				func.setBairro(rs.getString("bairro"));
				func.setEstado(rs.getString("estado"));
				func.setDtAdm(rs.getString("dtAdm"));
				func.setCargo(rs.getString("cargo"));
				func.setPorcentagem(rs.getDouble("porcentagem"));
				f.add(func);
			}
			rs.close();
			pstm.close();
			connection.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return f;
	}
	
	
	public void Alterar(Funcionario fun){
		String sql = "update funcionario set nome=? , dataNasc=? , cpf=? , rg=? , fone=? , celular=? , endereco=? , cidade = ? , bairro=? , estado=? , dtAdm=? , cargo=? , porcentagem=? where id=?";
		try {
			PreparedStatement pstm = connection.prepareStatement(sql);
			pstm.setString(1, fun.getNome());
			pstm.setString(2, fun.getDataNasc());
			pstm.setString(3, fun.getCpf());
			pstm.setString(4, fun.getRg());
			pstm.setString(5, fun.getFone());
			pstm.setString(6, fun.getCelular());
			pstm.setString(7, fun.getEndereco());
			pstm.setString(8, fun.getCidade());
			pstm.setString(9, fun.getBairro());
			pstm.setString(10, fun.getEstado());
			pstm.setString(11, fun.getDtAdm());
			pstm.setString(12, fun.getCargo());
			pstm.setDouble(13, fun.getPorcentagem());
			pstm.setInt(14, fun.getId());
			pstm.execute();
			pstm.close();
			connection.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
	public void deletar(Funcionario fun){
		String sql = "delete from funcionario where id=?";
		PreparedStatement pstm;
		try {
			pstm = connection.prepareStatement(sql);
			pstm.setInt(1, fun.getId());
			pstm.execute();
			pstm.close();
			connection.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	
	public List<Funcionario> ListarPorNome(String inf ,String rec){
		List<Funcionario> f = new ArrayList<Funcionario>();
		String sql = "select * from funcionario where "+rec+" like  ? ";
		try {
			PreparedStatement pstm = connection.prepareStatement(sql);
			pstm.setString(1,inf+"%");
			ResultSet rs = pstm.executeQuery();
			
			while(rs.next()){
				Funcionario func = new Funcionario();
				func.setId(rs.getInt("id"));
				func.setNome(rs.getString("nome"));
				func.setDataNasc(rs.getString("dataNasc"));
				func.setCpf(rs.getString("cpf"));
				func.setRg(rs.getString("rg"));
				func.setFone(rs.getString("fone"));
				func.setCelular(rs.getString("celular"));
				func.setEndereco(rs.getString("endereco"));
				func.setCidade(rs.getString("cidade"));
				func.setBairro(rs.getString("bairro"));
				func.setEstado(rs.getString("estado"));
				func.setDtAdm(rs.getString("dtAdm"));
				func.setCargo(rs.getString("cargo"));
				func.setPorcentagem(rs.getDouble("porcentagem"));
				f.add(func);
			}
			rs.close();
			pstm.close();
			connection.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return f;
	}
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
}
